# -*- coding: utf-8 -*-
import pymysql
import datetime
import json


def get_conn(dorsi_jdbc_json):
    conn = pymysql.connect(host=dorsi_jdbc_json.get('host'), port=dorsi_jdbc_json.get('port'),
                           user=dorsi_jdbc_json.get('user'), passwd=dorsi_jdbc_json.get('password'), db='jms_dwd',
                           charset='utf8')
    return conn


def execute(sql, dorsi_jdbc_json):
    conn = get_conn(dorsi_jdbc_json)
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.close()


def main(begin_date, dorsi_jdbc_pas):
    dorsi_jdbc_json = json.loads(dorsi_jdbc_pas.replace("'", '"'))
    begin_date = datetime.datetime.strptime(begin_date, "%Y-%m-%d")
    today = (begin_date + datetime.timedelta(days=-3)).strftime("%Y-%m-%d")
    partition = (begin_date + datetime.timedelta(days=-3)).strftime("%Y%m%d")
    droppartition = """TRUNCATE TABLE jms_dws.dws_whole_network_economic_circle PARTITION (p{partition});""".format(
        partition=partition)
    dws_whole_network_economic_circle = """INSERT INTO jms_dws.dws_whole_network_economic_circle 
SELECT
	* 
FROM
	(
	SELECT
		taking_date,
		1 AS type,
		count( 1 ) AS total,
		sum( CASE WHEN prescription = 0 THEN 1 ELSE 0 END ) AS zeroday_num,
		round( sum( CASE WHEN prescription = 0 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS zeroday_rate,
		sum( CASE WHEN prescription = 1 THEN 1 ELSE 0 END ) AS oneday_num,
		round( sum( CASE WHEN prescription = 1 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS oneday_rate,
		sum( CASE WHEN prescription = 2 THEN 1 ELSE 0 END ) AS twoday_num,
		round( sum( CASE WHEN prescription = 2 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS twoday_rate,
		sum( CASE WHEN prescription = 3 THEN 1 ELSE 0 END ) AS threeday_num,
		round( sum( CASE WHEN prescription = 3 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS threeday_rate 
	FROM
		jms_dwd.dwd_waybill_prescription_reach_details 
	WHERE
		taking_date = '{today}' 
		AND start_city_id IN ( '440600', '440300', '440100', '441300', '441900', '440400', '442000', '441200', '440700' ) 
	GROUP BY
		taking_date UNION ALL
	SELECT
		taking_date,
		2 AS type,
		count( 1 ) AS total,
		sum( CASE WHEN prescription = 0 THEN 1 ELSE 0 END ) AS zeroday_num,
		round( sum( CASE WHEN prescription = 0 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS zeroday_rate,
		sum( CASE WHEN prescription = 1 THEN 1 ELSE 0 END ) AS oneday_num,
		round( sum( CASE WHEN prescription = 1 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS oneday_rate,
		sum( CASE WHEN prescription = 2 THEN 1 ELSE 0 END ) AS twoday_num,
		round( sum( CASE WHEN prescription = 2 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS twoday_rate,
		sum( CASE WHEN prescription = 3 THEN 1 ELSE 0 END ) AS threeday_num,
		round( sum( CASE WHEN prescription = 3 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS threeday_rate 
	FROM
		jms_dwd.dwd_waybill_prescription_reach_details 
	WHERE
		taking_date = '{today}' 
		AND start_city_id IN (
			'330100',
			'310100',
			'331000',
			'330500',
			'330200',
			'320500',
			'320200',
			'330700',
			'321200',
			'320600',
			'330600',
			'320900',
			'321000',
			'320100',
			'330900',
			'321100',
			'330400',
			'320400',
			'340100',
			'340200',
			'340500',
			'340700',
			'340800',
			'341100',
			'341700',
			'341800' 
		) 
	GROUP BY
		taking_date 
	UNION ALL
	SELECT
		taking_date,
		3 AS type,
		count( 1 ) AS total,
		sum( CASE WHEN prescription = 0 THEN 1 ELSE 0 END ) AS zeroday_num,
		round( sum( CASE WHEN prescription = 0 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS zeroday_rate,
		sum( CASE WHEN prescription = 1 THEN 1 ELSE 0 END ) AS oneday_num,
		round( sum( CASE WHEN prescription = 1 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS oneday_rate,
		sum( CASE WHEN prescription = 2 THEN 1 ELSE 0 END ) AS twoday_num,
		round( sum( CASE WHEN prescription = 2 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS twoday_rate,
		sum( CASE WHEN prescription = 3 THEN 1 ELSE 0 END ) AS threeday_num,
		round( sum( CASE WHEN prescription = 3 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS threeday_rate 
	FROM
		jms_dwd.dwd_waybill_prescription_reach_details 
	WHERE
		taking_date = '{today}' 
		AND start_city_id IN ( '110100', '120100', '130100', '130200', '130400', '130600', '130900', '130500', '131000', '130800', '130700', '131100', '130300' ) 
	GROUP BY
		taking_date 
	) t; """.format(today=today)
    print('sql====================================' + dws_whole_network_economic_circle)
    execute(droppartition, dorsi_jdbc_json)
    execute(dws_whole_network_economic_circle, dorsi_jdbc_json)
